package org.azj.dbOperatorComponent;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.sql.DataSource;
import javax.swing.JOptionPane;

import org.azj.logHelper.LOG;
import org.azj.logHelper.LOG_COMPONENT;
import org.azj.logHelper.LOG_LEVEL;
import org.ht.global.Global;


public class OperableTable 
	extends DbQueryOperation implements IDbNonQueryOperation
{
	// fields
	private DatabaseMetaData dbMetaData = null;
	private DataSource dataSource = null;
	private String schema;
	private String tableName;
	private ResultSet tableInfoResultSet;
	private HashMap<String , IColumnProperty> columns;
	private HashMap<String , IColumnProperty> primaryKeys;
	private List<List<IForeignKeyColumnProperty>> foreignKeysSet;
	
	// constructor
	public OperableTable(DataSource dataSource, String schema, String tableName) throws SQLException
	{
		this.dataSource = dataSource;
		this.schema = schema;
		this.tableName = tableName;
		Connection conn = null;
		// 获取Meta Data
		try{
		conn = SingleThreadConnectionHolder.getConnection(this.dataSource);
		}catch (SQLException e){
			LOG.WriteLog(LOG_LEVEL.CRITICAL, LOG_COMPONENT.LOG_COMP_DB_CHANGE, "数据库连接失败");
			JOptionPane.showMessageDialog(Global.mainpanel, "数据库连接失败");
			
			throw e;
		}
		dbMetaData = conn.getMetaData();
		tableInfoResultSet = dbMetaData.getTables(null, schema, tableName, null); 
		
		// 检查schema.tableName是否合法
		if (tableInfoResultSet.next() == false)
		{
			// getTables其实是失败的
			String messageString = String.format(
					"表[%s.%s]并不是合法的名字 ", 
					schema, 
					tableName);
			LOG.WriteLog(LOG_LEVEL.CRITICAL, LOG_COMPONENT.LOG_COMP_DB_CHANGE, messageString);
			throw new SQLException(messageString);
		}
		columns = new HashMap<String , IColumnProperty>();
		primaryKeys = new HashMap<String , IColumnProperty>();
		foreignKeysSet = new ArrayList<List<IForeignKeyColumnProperty>>();	
	}
	
	// implements IOperableTable
	@Override
	public ICommand getInsertCommand(String contents) {
		return new InsertCommand(this.getTableFullName(), contents);
	}
	
	@Override
	public ICommand getInsertPartialCommand(HashMap<String, String> contents) {
		try {
			if (columns.isEmpty()) {
				columns = getAllColumns();
			}
		} catch (Exception e) {
			LOG.WriteLog(
					LOG_LEVEL.CRITICAL, 
					LOG_COMPONENT.LOG_COMP_DB_CHANGE, 
					"获取数据库列信息错误 : " + e.getMessage());
		}
		
		return new InsertPartialCommand(
				this.getTableFullName(), contents, columns, new ImageBlobStrategy());
	}

	@Override
	public ICommand getDeleteCommand(String whereFilter) {
		return new DeleteCommand(this.getTableFullName(), whereFilter);
	}

	@Override
	public ICommand getUpdateCommand(String whereFilter, String updateContents) {
		return new UpdateCommand(this.getTableFullName(), whereFilter, updateContents);
	}

	@Override
	public ICommand getUpdatePartialCommand(
			String whereFilter, HashMap<String, String> contents) {
		try {
			if (columns.isEmpty()) {
				columns = getAllColumns();
			}
		} catch (Exception e) {
			LOG.WriteLog(
					LOG_LEVEL.CRITICAL, 
					LOG_COMPONENT.LOG_COMP_DB_CHANGE, 
					"获取数据库列信息错误 : " + e.getMessage());
		}
		
		return new UpdatePartialCommand(
				this.getTableFullName(), contents, columns, whereFilter);
	}
	
	@Override 
	public ICommand getSelectCommand(SelectFilter selectFilter) {
		return super.setSelectFilter(selectFilter, this.getTableFullName());
	}
	
	@Override
	public String GetTableSchema() {
		return schema;
	}

	@Override
	public String getTableName() {
		return tableName;
	}

	// full name - Schema.Name
	public String getTableFullName(){
		return String.format("%s.%s", schema, tableName);
	}
	
	@Override
	public String getTableType() throws SQLException {
		return tableInfoResultSet.getString("TYPE_NAME");
	}

	@Override
	public HashMap<String , IColumnProperty> getAllColumns() throws SQLException {
		// 如果之前有过查询过列信息，则直接返回结果
		if (columns.size() > 0)
		{
			return columns;
		}
		
		ResultSet rs = dbMetaData.getColumns(null, schema, tableName, "%");
		while (rs.next())
		{
			String columnName = rs.getString("COLUMN_NAME");
			StringBuffer sbColName = new StringBuffer();
			sbColName.append(this.getTableFullName()).append(".").append(columnName);
			
			IColumnProperty columnProperty = 
					new ColumnProperty(
							this,
							sbColName.toString(),
							rs.getInt("DATA_TYPE"),
							rs.getString("TYPE_NAME"),
							rs.getInt("COLUMN_SIZE"),
							rs.getString("IS_NULLABLE").equals("YES"),
							rs.getString("COLUMN_DEF"),
							rs.getInt("ORDINAL_POSITION") - 1,
							rs.getString("IS_AUTOINCREMENT").equals("YES") );

			columns.put(sbColName.toString(), columnProperty);
		}
		rs.close();
		
		return columns;
	}

	@Override
	public IColumnProperty getColumn(String columnName) throws SQLException
	{
		// check where the input columnName is in its formal style
		String[] strs = columnName.split(".");
		String newcolName = columnName;
		if (strs.length == 1)
		{
			// only input as the last part of columnName, append the schema and tableName 
			newcolName = getTableFullName() + "." + strs[0];
		}
		newcolName = newcolName.toUpperCase();
		
		// 如果之前有过查询过列信息，则直接返回结果
		if (columns.size() == 0)
		{
			// 获取所有的columns
			getAllColumns();
		}
		
		// columns一定有值了
		return columns.get(newcolName.toUpperCase());
	}
	
	@Override
	public HashMap<String , IColumnProperty> getPrimaryKey() throws SQLException {
		// 如果之前有过查询过列信息，则直接返回结果
		if (primaryKeys.size() > 0)
		{
			return primaryKeys;
		}
		
		// 如果之前没有查询过列信息，则进行检查
		if (columns.size() == 0)
		{
			// 获取所有的columns
			getAllColumns();
		}
		
		// 逐个检查，匹配列名的就增加进主键集
		ResultSet rs = dbMetaData.getPrimaryKeys(null, schema, tableName);   
		while (rs.next()) 
        {
			IColumnProperty columnProperty = this.getColumn(rs.getString("COLUMN_NAME"));
			if (columnProperty != null)
			{
				String colName = columnProperty.getColumnFullName();
        		primaryKeys.put( colName, columns.get(colName) );
        	}
        }
		rs.close();
		
		return primaryKeys;
	}

	@Override
	public List<List<IForeignKeyColumnProperty>> getForeignKey()  throws SQLException {
		// 如果之前已经检查过foreignkey了，就直接返回
		if (foreignKeysSet.size() > 0)
		{
			return foreignKeysSet;
		}
			
		// get the imported keys
		ResultSet rs = dbMetaData.getImportedKeys(null, schema, tableName);
		
		// traverse the imported keys, with the "KEY_SEQ"'s value 1 is the 
		// signature of the new imported keys groups
		List<IForeignKeyColumnProperty> foreignKeysGroup = null;
        while (rs.next())   
        {   
        	short keySeq = rs.getShort("KEY_SEQ");
        	if (keySeq == 1)
        	{
        		if (foreignKeysGroup != null)
        		{
        			// we should not add a null into results
        			foreignKeysSet.add(foreignKeysGroup);
        		}
        		
        		// indicate a new imported keys group
        		foreignKeysGroup = new ArrayList<IForeignKeyColumnProperty>();	
        	}
        	
        	foreignKeysGroup.add(
        		new ForeignKeyColumnProperty(
        				this.dataSource, 
        				rs.getString("PKTABLE_SCHEM"), 
        				rs.getString("PKTABLE_NAME"), 
        				rs.getString("PKCOLUMN_NAME"), 
        				this, 
        				rs.getString("FKCOLUMN_NAME"), 
        				rs.getString("FK_NAME")) );
        }
        rs.close();
		
		return foreignKeysSet;
	}
}
